#基金指定区间涨跌幅对比
#入参 startDate endDate codeList sort(升序降序不排序)
#返回值
import pandas as pd
import numpy as np
import datetime
import DataBase

def run(dbutil,codeList,startDate,endDate,sort):

    # 获取时间段内所有的净值数据
    read_sql = "select net_values,week,date,convert_drifts from foundation_values " \
               "where  date >= %s and date<=%s and code = %s and net_values !=''"
    #存储每个基金在期间内的涨跌幅
    resultList = []
    #计算区间涨跌幅
    for code in codeList:
        condition=[]
        condition.append(startDate)
        condition.append(endDate)
        condition.append(code)
        df = pd.read_sql(read_sql, con=dbutil.conn, params=condition)
        if df.empty==True:
            pass
            # print(code)
        else:
            df['index_date'] = pd.to_datetime(df['date'])  # 新增一个索引列,将数据类型转换为日期类型
            df = df.set_index('index_date')  # 将date设置为index
            # 按行的索引按降序进行排序,日期由大到小,根据索引获取时间点内的数据
            df = df.sort_index(ascending=False)
            maxday_value = float(df.iloc[0][0])  # 日期最大那天的净值
            minday_value = float(df.iloc[-1][0])  # 日期最小那天的净值
            min_day = df.iloc[-1][2]  # 日期最小的那天
            for label in df.index:
                date = df.loc[label].values[2]  # 日期
                convert_drifts = df.loc[label].values[3]  # 是否分红,若为空则表示不分红
                if date != min_day:
                    if convert_drifts.startswith('每份派现金'):
                        maxday_value += float(convert_drifts[5:-1])
                    elif convert_drifts.startswith('每份基金份额折算'):
                        maxday_value *= float(convert_drifts[8:-1])
            changeRate = round((maxday_value - minday_value) / minday_value * 100, 2)
            result = dict(code=code, changeRate=changeRate)
        resultList.append(result)
    #做排序
    r = pd.DataFrame(resultList)
    r['index'] = r['changeRate']  # 新增一个索引列
    r = r.set_index('index')  # 将changeRate设置为index
    # 按行的索引按降序进行排序
    r = r.sort_index(ascending=False)
    print(r)
    return np.array(r).tolist()



if __name__=="__main__":
    dbutil = DataBase.DataBase('test')
    sql="select code from foundation_position where report_date='2017-12-31' and convert(left(proportion_of_bond,CHAR_LENGTH(proportion_of_bond)-1),decimal(18,2))>=50.00 and proportion_of_bond!='---' and code IN " \
        " (select code from foundation_position where report_date='2018-09-30' and convert(left(proportion_of_bond,CHAR_LENGTH(proportion_of_bond)-1),decimal(18,2))>=50.00) and proportion_of_bond!='---'"
    records = dbutil.select_list(sql)
    codeList = []
    for data in records:
        codeList.append(data[0])
    # codeList=[]
    # codeList.append('000001')
    # codeList.append('000005')
    # codeList.append('000007')
    startDate='2018-01-01'
    endDate ='2018-12-31'
    a = run(dbutil,codeList,startDate,endDate,None)